﻿Imports System
Imports System.IO
Imports System.Net
Imports System.Windows
Imports System.Windows.Controls
Imports System.Windows.Data
Imports System.Windows.Media
Imports System.Windows.Media.Animation
Imports System.Windows.Navigation
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Windows.Controls
Imports System.Globalization
Imports Microsoft.VisualBasic
Imports Microsoft.Windows.Controls.Primitives
Imports System.ComponentModel
Imports System.Collections.Generic
Imports System.Linq
Imports System.Threading
Imports System.Windows.Threading

Public Class coord
	Public c As Long

	Public Sub New(ByVal Column As Integer, ByVal Row As Integer, ByVal ColumnCount As Integer)
		c = (Row * ColumnCount) + Column
	End Sub
End Class

Public Class ResultData
	Public Shared ds As DataSet
	Public Shared dtFinal As DataTable
	Public Shared dvFinal As DataView
	Public Shared CoordDifferences As New Collections.Generic.List(Of Long)

	Public Shared Function GetCoordID(ByVal Row As Integer, ByVal Column As Integer, ByVal ColumnCount As Integer) As Long
		Return (Row * ColumnCount) + Column
	End Function
End Class

Partial Public Class CellHighlighterConverter
	Implements IMultiValueConverter

	Public Shared bgSame As New SolidColorBrush(Colors.White)
	Public Shared bgDifferent As New SolidColorBrush(Colors.Yellow)
	Public Shared bgMissing As New SolidColorBrush(Color.FromArgb(25, 0, 0, 0))
	Public Shared fgMissing As New SolidColorBrush(Color.FromArgb(255, 150, 150, 150))
	Public Shared bgA As New SolidColorBrush(Color.FromArgb(255, 200, 0, 0))
	Public Shared bgLightA As New SolidColorBrush(Color.FromArgb(15, 200, 0, 0))
	Public Shared fgA As New SolidColorBrush(Color.FromArgb(255, 200, 0, 0))
	Public Shared bgB As New SolidColorBrush(Color.FromArgb(255, 0, 0, 200))
	Public Shared bgLightB As New SolidColorBrush(Color.FromArgb(15, 0, 0, 200))
	Public Shared fgB As New SolidColorBrush(Color.FromArgb(255, 0, 0, 200))
	Public Shared bgGray As New SolidColorBrush(Color.FromArgb(50, 0, 0, 0))

	Public Shared Sub FreezeStyles()
		bgSame.Freeze()
		bgDifferent.Freeze()
		bgMissing.Freeze()
		fgMissing.Freeze()
		bgA.Freeze()
		fgA.Freeze()
		bgB.Freeze()
		fgB.Freeze()
	End Sub

	Public Function Convert(ByVal values() As Object, ByVal targetType As System.Type, ByVal parameter As Object, ByVal culture As System.Globalization.CultureInfo) As Object Implements System.Windows.Data.IMultiValueConverter.Convert
		If (values(0).GetType Is GetType(DataGridCell)) Then
			If (values(1).GetType Is GetType(DataRow)) Then
				Dim cell As DataGridCell = DirectCast(values(0), DataGridCell)
				Dim columnNumber As Integer = cell.Column.DisplayIndex
				Dim row As DataRow = DirectCast(values(1), DataRow)
				Dim db As String = ""
				cell.ToolTip = Nothing

				Dim rowId As Integer = CInt(row(0))
				If rowId >= 0 Then
					db = CStr(row(1))

					If db = "B" Then
						cell.Margin = New Thickness(0, 0, 0, 5)
					End If

					If columnNumber > 0 Then
						If Not row.RowError = "Blank" Then
							If row(columnNumber + 1) Is DBNull.Value OrElse row(columnNumber + 1) Is Nothing Then
								cell.Foreground = fgMissing
							Else
								If db = "A" Then
									cell.Foreground = fgA
								Else
									cell.Foreground = fgB
								End If
							End If

							If ResultData.CoordDifferences.Contains(ResultData.GetCoordID(rowId, columnNumber, ResultData.dvFinal.Table.Columns.Count - 1)) Then
								cell.ToolTip = "Different"
								Return bgDifferent
							Else
								If db = "A" Then
									Return bgLightA
								Else
									Return bgLightB
								End If
							End If
						Else
							cell.ToolTip = "Row does not exist in this table"
							cell.Foreground = fgMissing
							Return bgMissing
						End If
					Else
						' Database
						cell.Foreground = bgSame

						If row.RowError = "Blank" Then
							Return bgGray
						Else
							If db = "A" Then
								Return bgA
							Else
								Return bgB
							End If
						End If
					End If
				End If
			End If
			Return bgSame
		Else
			Return Nothing
		End If
	End Function

	Public Function ConvertBack(ByVal value As Object, ByVal targetTypes() As System.Type, ByVal parameter As Object, ByVal culture As System.Globalization.CultureInfo) As Object() Implements System.Windows.Data.IMultiValueConverter.ConvertBack
		Throw New System.NotImplementedException()
	End Function
End Class

Partial Public Class Results
	Implements INotifyPropertyChanged

	'Private LoadResultsThread As System.Threading.Thread
	Private cmd As SqlCommand
	Private da As SqlDataAdapter

	Private _DB_A As Char = CChar("A")
	Private _DB_B As Char = CChar("B")
	Private strSqlServerNameA As String
	Private strSqlServerNameB As String
	Private strDatabaseNameA As String
	Private strDatabaseNameB As String
	Private strDatabaseAndTableNameA As String
	Private strDatabaseAndTableNameB As String
	Private _TableA As String
	Private _TableB As String
	Private _KeyA As String
	Private _KeyB As String
	Private iTotalRowCount As Integer
	'Private bLoaded As Boolean = False

	Public Property KeyA() As String
		Get
			Return _KeyA
		End Get
		Set(ByVal value As String)
			_KeyA = value
		End Set
	End Property

	Public Property KeyB() As String
		Get
			Return _KeyB
		End Get
		Set(ByVal value As String)
			_KeyB = value
		End Set
	End Property

	Public Property TableA() As String
		Get
			Return _TableA
		End Get
		Set(ByVal value As String)
			_TableA = value
		End Set
	End Property

	Public Property TableB() As String
		Get
			Return _TableB
		End Get
		Set(ByVal value As String)
			_TableB = value
		End Set
	End Property

	Public Property SqlServerNameA() As String
		Get
			Return strSqlServerNameA
		End Get
		Set(ByVal value As String)
			strSqlServerNameA = value
			NotifyPropertyChanged("SqlServerNameA")
		End Set
	End Property

	Public Property SqlServerNameB() As String
		Get
			Return strSqlServerNameB
		End Get
		Set(ByVal value As String)
			strSqlServerNameB = value
			NotifyPropertyChanged("SqlServerNameB")
		End Set
	End Property

	Public Property DatabaseNameA() As String
		Get
			Return strDatabaseNameA
		End Get
		Set(ByVal value As String)
			strDatabaseNameA = value
			NotifyPropertyChanged("DatabaseNameA")
		End Set
	End Property

	Public Property DatabaseNameB() As String
		Get
			Return strDatabaseNameB
		End Get
		Set(ByVal value As String)
			strDatabaseNameB = value
			NotifyPropertyChanged("DatabaseNameB")
		End Set
	End Property

	Public Property DatabaseAndTableNameA() As String
		Get
			Return strDatabaseAndTableNameA
		End Get
		Set(ByVal value As String)
			strDatabaseAndTableNameA = value
			NotifyPropertyChanged("DatabaseAndTableNameA")
		End Set
	End Property

	Public Property DatabaseAndTableNameB() As String
		Get
			Return strDatabaseAndTableNameB
		End Get
		Set(ByVal value As String)
			strDatabaseAndTableNameB = value
			NotifyPropertyChanged("DatabaseAndTableNameB")
		End Set
	End Property

	Public ReadOnly Property DifferenceCount() As String
		Get
			If ResultData.dtFinal IsNot Nothing Then
				Dim iDifferenceCount As Integer = 0

				If ResultData.dvFinal IsNot Nothing AndAlso ResultData.dvFinal.Count > 0 Then
					iDifferenceCount = ResultData.dvFinal.Count
				End If

				If iTotalRowCount > 0 Then
					Return String.Format("{0} rows compared.  {1} rows have differences.", FormatNumber(iTotalRowCount, 0, TriState.UseDefault, TriState.UseDefault, TriState.True), FormatNumber(iDifferenceCount, 0, TriState.UseDefault, TriState.UseDefault, TriState.True))
				Else
					Return "0 rows compared."
				End If
			Else
				Return ""
			End If
		End Get
	End Property

	Public Shared ReadOnly IsComparingProperty As DependencyProperty = DependencyProperty.Register("IsComparing", _
																																														 GetType(Boolean), _
																																														 GetType(Results))
	Public Property IsComparing() As Boolean
		Get
			Return DirectCast(Me.GetValue(IsComparingProperty), Boolean)
		End Get
		Set(ByVal value As Boolean)
			Me.SetValue(IsComparingProperty, value)
		End Set
	End Property

	Public Event PropertyChanged As PropertyChangedEventHandler Implements INotifyPropertyChanged.PropertyChanged
	Protected Sub NotifyPropertyChanged(ByVal propertyName As String)
		RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))
	End Sub

	Public Sub New()
		MyBase.New()

		Me.InitializeComponent()

		CellHighlighterConverter.FreezeStyles()

		' Insert code required on object creation below this point.
		LayoutRoot.DataContext = Me
	End Sub

	Private Sub btnRefresh_Click() Handles btnRefresh.Click
		Try
			If btnRefresh.Content.ToString = "Cancel" Then
				'LoadResultsThread.Abort()
				LoadResultsBackgroundWorker.CancelAsync()
				Base.WriteToConsole("Cancelling...")
				ResultsLoading.StopLoading()
				ResultsLoading.Visibility = Windows.Visibility.Hidden
				'If cmd IsNot Nothing Then
				'	cmd.Cancel()
				'	cmd.Dispose()
				'End If
				'If da IsNot Nothing Then
				'	da.Dispose()
				'End If
			Else
				StartLoadResults()
			End If
		Catch ex As Exception
			Base.WriteToConsole(ex.ToString)
		End Try
	End Sub

	Private WithEvents LoadResultsBackgroundWorker As BackgroundWorker

	Public Sub StartLoadResults()
		Try
			Base.ReOpenConnections()

			'If LoadResultsThread Is Nothing OrElse Not LoadResultsThread.IsAlive Then
			If LoadResultsBackgroundWorker Is Nothing OrElse Not LoadResultsBackgroundWorker.IsBusy Then
				IsComparing = True
				dgResults.ItemsSource = Nothing
				dgResults.Columns.Clear()

				ResultsLoading.StartLoading()
				ResultsLoading.Visibility = Windows.Visibility.Visible

				'LoadResultsThread = New Thread(AddressOf LoadResults)
				'LoadResultsThread.SetApartmentState(ApartmentState.MTA)
				'LoadResultsThread.Priority = ThreadPriority.Normal
				'LoadResultsThread.IsBackground = True
				'LoadResultsThread.Start()
				LoadResultsBackgroundWorker = New BackgroundWorker
				LoadResultsBackgroundWorker.WorkerSupportsCancellation = True
				LoadResultsBackgroundWorker.RunWorkerAsync()

				btnRefresh.Content = "Cancel"
			End If
		Catch ex As Exception
			Base.WriteToConsole(ex.ToString)
		End Try
	End Sub

	Private Sub LoadResultsBackgroundWorker_DoWork(ByVal sender As Object, ByVal e As DoWorkEventArgs) Handles LoadResultsBackgroundWorker.DoWork
		e.Result = LoadResults()
	End Sub

	Private Sub LoadResultsBackgroundWorker_Completed(ByVal sender As Object, ByVal e As RunWorkerCompletedEventArgs) Handles LoadResultsBackgroundWorker.RunWorkerCompleted
		IsComparing = False
		btnRefresh.Content = "Refresh"
		ResultsLoading.StopLoading()
		ResultsLoading.Visibility = Windows.Visibility.Hidden

		If e.Cancelled OrElse e.Result.Equals(False) Then
			Base.WriteToConsole("Comparison cancelled")
			ClearAll()
		Else
			Base.WriteToConsole("Comparison completed in " & FormatNumber(ResultsLoading.tsElapsedTime.TotalSeconds, 2) & " seconds")
		End If
	End Sub

	Private Sub ClearAll()
		If ResultData.ds IsNot Nothing Then
			ResultData.ds.Clear()
			ResultData.ds.Dispose()
		End If

		If ResultData.dtFinal IsNot Nothing Then
			ResultData.dtFinal.Clear()
			ResultData.dtFinal.Dispose()
		End If

		If ResultData.dvFinal IsNot Nothing Then
			ResultData.dvFinal.Dispose()
		End If

		If ResultData.CoordDifferences IsNot Nothing Then
			ResultData.CoordDifferences.Clear()
		End If

		iTotalRowCount = 0

		NotifyPropertyChanged("DifferenceCount")
	End Sub

	Private Function LoadResults() As Boolean
		LoadResults = False

		Try
			ClearAll()

			ResultData.ds = New DataSet
			ResultData.dtFinal = New DataTable

			Try
				SqlServerNameA = Base.sqlConn(_DB_A).DataSource
				DatabaseAndTableNameA = DatabaseNameA & "." & _TableA
				SqlServerNameB = Base.sqlConn(_DB_B).DataSource
				DatabaseAndTableNameB = DatabaseNameB & "." & _TableB

				If _KeyA > "" AndAlso _KeyB > "" Then

					If Base.bQuickMode Then
						Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New Base.WriteToConsoleCallback(AddressOf Base.WriteToConsole), "Performing comparison in quick mode...", False)
						QuickCompare()
					Else
						Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New Base.WriteToConsoleCallback(AddressOf Base.WriteToConsole), "Performing comparison in standard mode...", False)
						StandardCompare()
					End If

					If _KeyA = _KeyB Then
						ResultData.dvFinal = New DataView(ResultData.dtFinal, "", _KeyA & ", {DB}", DataViewRowState.CurrentRows)
					Else
						ResultData.dvFinal = New DataView(ResultData.dtFinal, "", _KeyA & ", " & _KeyB & ", {DB}", DataViewRowState.CurrentRows)
					End If

					For row As Integer = ResultData.dvFinal.Count - 2 To 0 Step -2
						' if neither row is blank then check for differences
						If Not (ResultData.dvFinal(row).Row.RowError = "Blank" Xor ResultData.dvFinal(row + 1).Row.RowError = "Blank") Then
							Dim bDifferent As Boolean = False
							For col As Integer = 2 To ResultData.dvFinal.Table.Columns.Count - 1
								If Not ResultData.dvFinal(row)(col).Equals(ResultData.dvFinal(row + 1)(col)) Then
									' add differences
									ResultData.CoordDifferences.Add(ResultData.GetCoordID(CInt(ResultData.dvFinal.Item(row)(0)), col - 1, ResultData.dvFinal.Table.Columns.Count - 1))
									ResultData.CoordDifferences.Add(ResultData.GetCoordID(CInt(ResultData.dvFinal.Item(row + 1)(0)), col - 1, ResultData.dvFinal.Table.Columns.Count - 1))
									bDifferent = True
								End If
							Next

							If Not bDifferent Then
								' delete matches 
								ResultData.dvFinal.Item(row).Row.Delete()
								ResultData.dvFinal.Item(row).Row.Delete()
							End If
						End If
					Next

					If Not LoadResultsBackgroundWorker.CancellationPending Then
						'Display Results
						Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New DisplayResultsCallback(AddressOf Me.DisplayResults))
					End If
				Else
					Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New Base.WriteToConsoleCallback(AddressOf Base.WriteToConsole), "No primary keys or unique columns selected.  Cannot perform comparison.", True)
				End If

				LoadResults = Not LoadResultsBackgroundWorker.CancellationPending
			Catch taex As ThreadAbortException
				' do nothing
			Catch ex As Exception
				Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New Base.WriteToConsoleCallback(AddressOf Base.WriteToConsole), ex.ToString, True)
			End Try
		Catch taex As ThreadAbortException
			' do nothing
		Catch ex As Exception
			Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New Base.WriteToConsoleCallback(AddressOf Base.WriteToConsole), ex.ToString, True)
		End Try

		If Not LoadResultsBackgroundWorker.CancellationPending Then
			NotifyPropertyChanged("DifferenceCount")
		End If
	End Function

	Private Sub QuickCompare()
		' Start Loading
		Dim iStart As Integer
		Dim iRowsAffected As Integer = 0
		Dim iMaxRows As Integer = 100

		' Database A
		Base.sqlConn(_DB_A).ChangeDatabase(DatabaseNameA)
		cmd = New SqlCommand("SELECT COUNT(*) FROM " & DatabaseNameA & ".dbo.[" & Base.TableForQuery(_TableA) & "];", Base.sqlConn(_DB_A))
		iTotalRowCount = CInt(Base.NotNull(cmd.ExecuteScalar(), 0))
		cmd = New SqlCommand("SELECT TOP 1 * FROM " & DatabaseNameA & ".dbo.[" & Base.TableForQuery(_TableA) & "];", Base.sqlConn(_DB_A))
		da = New SqlDataAdapter(cmd)
		ResultData.ds.Tables.Add("A")
		da.FillSchema(ResultData.ds.Tables("A"), SchemaType.Source)
		da.FillSchema(ResultData.dtFinal, SchemaType.Source)
		Dim dtColumnsA As DataTable = Base.sqlConn(_DB_A).GetSchema(SqlClientMetaDataCollectionNames.Columns)

		' Database B
		Base.sqlConn(_DB_B).ChangeDatabase(DatabaseNameB)
		cmd = New SqlCommand("SELECT COUNT(*) FROM " & DatabaseNameB & ".dbo.[" & Base.TableForQuery(_TableB) & "];", Base.sqlConn(_DB_A))
		iTotalRowCount += CInt(Base.NotNull(cmd.ExecuteScalar(), 0))
		cmd = New SqlCommand("SELECT TOP 1 * FROM " & DatabaseNameB & ".dbo.[" & Base.TableForQuery(_TableB) & "];", Base.sqlConn(_DB_A))
		da = New SqlDataAdapter(cmd)
		ResultData.ds.Tables.Add("B")
		da.FillSchema(ResultData.ds.Tables("B"), SchemaType.Source)
		da.FillSchema(ResultData.dtFinal, SchemaType.Source)
		Dim dtColumnsB As DataTable = Base.sqlConn(_DB_A).GetSchema(SqlClientMetaDataCollectionNames.Columns)


		Dim dcSide As New DataColumn("{DB}", GetType(String))
		ResultData.dtFinal.Columns.Add(dcSide)
		dcSide.SetOrdinal(0)

		Dim sbCommonColumns As New Text.StringBuilder
		Dim sbColumnsA As New Text.StringBuilder
		Dim sbColumnsB As New Text.StringBuilder
		Dim sbSelectStatement As New Text.StringBuilder

		' Prepare the select statement to make the comparison at the SQL Server
		For Each dc As DataColumn In ResultData.dtFinal.Columns
			If Not dc.ColumnName = "{DB}" Then
				If sbCommonColumns.Length > 0 Then
					sbCommonColumns.Append(", ")
					sbColumnsA.Append(", ")
					sbColumnsB.Append(", ")
				End If
				sbCommonColumns.Append("[" & dc.ColumnName & "] ")

				If ResultData.ds.Tables("A").Columns.Contains(dc.ColumnName) Then
					Dim col() As DataRow = dtColumnsA.Select("TABLE_CATALOG = '" & DatabaseNameA & "' AND TABLE_NAME = '" & _TableA & "' AND COLUMN_NAME = '" & dc.ColumnName & "'")
					If col IsNot Nothing AndAlso col.Length > 0 AndAlso col(0)("CHARACTER_MAXIMUM_LENGTH") IsNot DBNull.Value AndAlso CInt(col(0)("CHARACTER_MAXIMUM_LENGTH")) > 8000 Then
						sbColumnsA.Append("CONVERT(nvarchar, [" & dc.ColumnName & "], 8000) AS [" & dc.ColumnName & "] ")
					Else
						sbColumnsA.Append("[" & dc.ColumnName & "] ")
					End If
				Else
					sbColumnsA.Append("NULL AS [" & dc.ColumnName & "] ")
				End If

				If ResultData.ds.Tables("B").Columns.Contains(dc.ColumnName) Then
					Dim col() As DataRow = dtColumnsB.Select("TABLE_CATALOG = '" & DatabaseNameB & "' AND TABLE_NAME = '" & _TableB & "' AND COLUMN_NAME = '" & dc.ColumnName & "'")
					If col IsNot Nothing AndAlso col.Length > 0 AndAlso col(0)("CHARACTER_MAXIMUM_LENGTH") IsNot DBNull.Value AndAlso CInt(col(0)("CHARACTER_MAXIMUM_LENGTH")) > 8000 Then
						sbColumnsB.Append("CONVERT(nvarchar, [" & dc.ColumnName & "], 8000) AS [" & dc.ColumnName & "] ")
					Else
						sbColumnsB.Append("[" & dc.ColumnName & "] ")
					End If
				Else
					sbColumnsB.Append("NULL AS [" & dc.ColumnName & "] ")
				End If
			End If
		Next

		sbSelectStatement.Append("SELECT MIN([{DB}]) AS [{DB}], ")
		sbSelectStatement.Append(sbCommonColumns)
		sbSelectStatement.Append(vbCrLf)
		sbSelectStatement.Append("FROM (")
			sbSelectStatement.Append("SELECT 'A' AS [{DB}], ")
			sbSelectStatement.Append(sbColumnsA)
			sbSelectStatement.Append(vbCrLf)
			sbSelectStatement.Append("FROM " & DatabaseNameA & ".dbo." & _TableA & " A ")
			sbSelectStatement.Append(vbCrLf)
		sbSelectStatement.Append("UNION ALL ")
			sbSelectStatement.Append(vbCrLf)
			sbSelectStatement.Append("SELECT 'B' AS [{DB}], ")
			sbSelectStatement.Append(sbColumnsB)
			sbSelectStatement.Append(vbCrLf)
			sbSelectStatement.Append("FROM " & DatabaseNameB & ".dbo." & _TableB & " A ")
			sbSelectStatement.Append(vbCrLf)
		sbSelectStatement.Append(") tmp ")
		sbSelectStatement.Append("GROUP BY ")
		sbSelectStatement.Append(sbCommonColumns)
		sbSelectStatement.Append(vbCrLf)
		sbSelectStatement.Append("HAVING COUNT(*) = 1 ")
		sbSelectStatement.Append("ORDER BY ")
		If _KeyA = _KeyB Then
			sbSelectStatement.Append(_KeyA)
		Else
			sbSelectStatement.Append(_KeyA & ", " & _KeyB)
		End If
		sbSelectStatement.Append(", [{DB}]")

		'Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New Base.WriteToConsoleCallback(AddressOf Base.WriteToConsole), sbSelectStatement.ToString, False)

		' Prepare the final datatable
		For Each dc As DataColumn In ResultData.dtFinal.Columns
			dc.AutoIncrement = False
			dc.ReadOnly = False
			dc.AllowDBNull = True
		Next

		Dim dcKey(1) As DataColumn
		dcKey(0) = New DataColumn("id986435")
		dcKey(0).AutoIncrement = True
		dcKey(0).AutoIncrementStep = 1
		dcKey(0).DataType = GetType(Int32)
		ResultData.dtFinal.Columns.Add(dcKey(0))
		dcKey(0).SetOrdinal(0)
		ResultData.dtFinal.PrimaryKey = dcKey

		cmd = New SqlCommand(sbSelectStatement.ToString, Base.sqlConn(_DB_A))
		da = New SqlDataAdapter(cmd)
		'da.Fill(ResultData.dtFinal)
		iStart = 0
		Do
			iRowsAffected = da.Fill(iStart, iMaxRows, ResultData.dtFinal)
			iStart += iRowsAffected
			If LoadResultsBackgroundWorker.CancellationPending Then
				Exit Do
			End If
		Loop While iRowsAffected >= iMaxRows

		' Fill in the blanks
		Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New SetResultsLoadingCallback(AddressOf Me.SetResultsLoading), 0)
		Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New SetResultsLoadingLabelCallback(AddressOf Me.SetResultsLoadingLabel), "Comparing...")

		Dim iTotalRows As Integer = ResultData.dtFinal.Rows.Count

		For i As Integer = 0 To iTotalRows - 1 Step 1
			If i = iTotalRows - 1 Then
				If ResultData.dtFinal.Rows(i)(1).ToString() = "A" Then
					Dim drNew As DataRow = ResultData.dtFinal.NewRow
					drNew.RowError = "Blank"
					drNew(_KeyB) = ResultData.dtFinal.Rows(i)(_KeyA)
					drNew("{DB}") = "B"
					ResultData.dtFinal.Rows.Add(drNew)
				End If
			ElseIf ResultData.dtFinal.Rows(i)(1).Equals(ResultData.dtFinal.Rows(i + 1)(1)) Then
				If ResultData.dtFinal.Rows(i)(1).ToString() = "A" Then
					Dim drNew As DataRow = ResultData.dtFinal.NewRow
					drNew.RowError = "Blank"
					drNew(_KeyB) = ResultData.dtFinal.Rows(i)(_KeyA)
					drNew("{DB}") = "B"
					ResultData.dtFinal.Rows.Add(drNew)
				Else
					Dim drNew As DataRow = ResultData.dtFinal.NewRow
					drNew.RowError = "Blank"
					drNew(_KeyA) = ResultData.dtFinal.Rows(i + 1)(_KeyB)
					drNew("{DB}") = "A"
					ResultData.dtFinal.Rows.Add(drNew)
				End If
			ElseIf i = 0 Then
				If ResultData.dtFinal.Rows(i)(1).ToString() = "B" Then
					Dim drNew As DataRow = ResultData.dtFinal.NewRow
					drNew.RowError = "Blank"
					drNew(_KeyA) = ResultData.dtFinal.Rows(i)(_KeyB)
					drNew("{DB}") = "A"
					ResultData.dtFinal.Rows.Add(drNew)
				End If
			Else
				'i += 1
			End If

			If i Mod 200 = 0 Then
				Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New SetResultsLoadingCallback(AddressOf Me.SetResultsLoading), (i / iTotalRows) * 100)
			End If
		Next
	End Sub

	Private Sub StandardCompare()
		' Start Loading
		Dim iStart As Integer
		Dim iRowsAffected As Integer = 0
		Dim iMaxRows As Integer = 100

		' Connection A
		Base.sqlConn(_DB_A).ChangeDatabase(DatabaseNameA)
		cmd = New SqlCommand("SELECT * FROM [" & Base.TableForQuery(_TableA) & "];", Base.sqlConn(_DB_A))
		da = New SqlDataAdapter(cmd)
		ResultData.ds.Tables.Add("A")
		da.FillSchema(ResultData.ds.Tables("A"), SchemaType.Source)
		da.FillSchema(ResultData.dtFinal, SchemaType.Source)
		'da.Fill(ResultData.ds.Tables("A"))

		iStart = 0
		Do
			iRowsAffected = da.Fill(iStart, iMaxRows, ResultData.ds.Tables("A"))
			iStart += iRowsAffected
			If LoadResultsBackgroundWorker.CancellationPending Then
				Exit Do
			End If
		Loop While iRowsAffected >= iMaxRows

		Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New SetResultsLoadingCallback(AddressOf Me.SetResultsLoading), 50)


		' Connection B
		Base.sqlConn(_DB_B).ChangeDatabase(DatabaseNameB)
		cmd = New SqlCommand("SELECT * FROM [" & Base.TableForQuery(_TableB) & "];", Base.sqlConn(_DB_B))
		da = New SqlDataAdapter(cmd)
		ResultData.ds.Tables.Add("B")
		da.FillSchema(ResultData.ds.Tables("B"), SchemaType.Source)
		da.FillSchema(ResultData.dtFinal, SchemaType.Source)
		'da.Fill(ResultData.ds.Tables("B"))

		iStart = 0
		Do
			iRowsAffected = da.Fill(iStart, iMaxRows, ResultData.ds.Tables("B"))
			iStart += iRowsAffected
			If LoadResultsBackgroundWorker.CancellationPending Then
				Exit Do
			End If
		Loop While iRowsAffected >= iMaxRows
		Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New SetResultsLoadingCallback(AddressOf Me.SetResultsLoading), 100)


		For Each dc As DataColumn In ResultData.dtFinal.Columns
			dc.AutoIncrement = False
			dc.ReadOnly = False
			dc.AllowDBNull = True
		Next

		'strCommonPrimaryKey = ResultData.ds.Tables("A").PrimaryKey(0).ColumnName
		'Base.WriteToConsole(Me, "Common primary key: " & strCommonPrimaryKey)
		Dim dcKeyA(1) As DataColumn
		If ResultData.ds.Tables("A").Columns(_KeyA).Unique Then
			dcKeyA(0) = ResultData.ds.Tables("A").Columns(_KeyA)
			ResultData.ds.Tables("A").PrimaryKey = dcKeyA
		Else
			dcKeyA(0) = Nothing
		End If

		Dim dcKeyB(1) As DataColumn
		If ResultData.ds.Tables("B").Columns(_KeyB).Unique Then
			dcKeyB(0) = ResultData.ds.Tables("B").Columns(_KeyB)
			ResultData.ds.Tables("B").PrimaryKey = dcKeyB
		Else
			dcKeyB(0) = Nothing
		End If

		Dim dcKey(1) As DataColumn
		dcKey(0) = New DataColumn("id986435")
		dcKey(0).AutoIncrement = True
		dcKey(0).AutoIncrementStep = 1
		dcKey(0).DataType = GetType(Int32)
		ResultData.dtFinal.Columns.Add(dcKey(0))
		dcKey(0).SetOrdinal(0)
		ResultData.dtFinal.PrimaryKey = dcKey

		Dim dcSide As New DataColumn("{DB}", GetType(String))
		ResultData.dtFinal.Columns.Add(dcSide)
		dcSide.SetOrdinal(1)

		' Start comparing
		Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New SetResultsLoadingCallback(AddressOf Me.SetResultsLoading), 0)
		Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New SetResultsLoadingLabelCallback(AddressOf Me.SetResultsLoadingLabel), "Comparing...")

		Dim iTotalRows As Integer = ResultData.ds.Tables("A").Rows.Count + ResultData.ds.Tables("B").Rows.Count
		iTotalRowCount = iTotalRows
		Dim iRow As Integer = 0
		For Each dr As DataRow In ResultData.ds.Tables("A").Rows
			ResultData.dtFinal.ImportRow(dr)
			ResultData.dtFinal.Rows(ResultData.dtFinal.Rows.Count - 1)("{DB}") = "A"

			Dim objKeyA As Object = dr(_KeyA)
			Dim bRowFound As Boolean = False

			If dcKeyB(0) IsNot Nothing Then
				bRowFound = ResultData.ds.Tables("B").Rows.Find(objKeyA) IsNot Nothing
			Else
				Dim otherRows As IEnumerable(Of Object) = (From row In ResultData.ds.Tables("B") Where row(_KeyB).Equals(objKeyA) Select row(_KeyB)).Take(1)
				bRowFound = (otherRows.Count > 0)
			End If

			If Not bRowFound Then
				' Insert blank row
				Dim drNew As DataRow = ResultData.dtFinal.NewRow
				drNew.RowError = "Blank"
				drNew(_KeyB) = objKeyA
				drNew("{DB}") = "B"
				ResultData.dtFinal.Rows.Add(drNew)
			End If
			iRow += 1

			If iRow Mod 200 = 0 Then
				Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New SetResultsLoadingCallback(AddressOf Me.SetResultsLoading), (iRow / iTotalRows) * 100)
				If LoadResultsBackgroundWorker.CancellationPending Then
					Exit For
				End If
			End If
		Next

		For Each dr As DataRow In ResultData.ds.Tables("B").Rows
			Dim objKeyB As Object = dr(_KeyB)
			Dim bRowFound As Boolean = False

			If dcKeyA(0) IsNot Nothing Then
				bRowFound = ResultData.ds.Tables("A").Rows.Find(objKeyB) IsNot Nothing
			Else
				Dim otherRows As IEnumerable(Of Object) = (From row In ResultData.ds.Tables("A") Where row(_KeyA).Equals(objKeyB) Select row(_KeyA)).Take(1)
				bRowFound = (otherRows.Count > 0)
			End If

			If Not bRowFound Then
				' Insert blank row
				Dim drNew As DataRow = ResultData.dtFinal.NewRow
				drNew.RowError = "Blank"
				drNew(_KeyA) = objKeyB
				drNew("{DB}") = "A"
				ResultData.dtFinal.Rows.Add(drNew)
			End If

			ResultData.dtFinal.ImportRow(dr)
			ResultData.dtFinal.Rows(ResultData.dtFinal.Rows.Count - 1)("{DB}") = "B"
			iRow += 1

			If iRow Mod 200 = 0 Then
				Dispatcher.Invoke(Windows.Threading.DispatcherPriority.Render, New SetResultsLoadingCallback(AddressOf Me.SetResultsLoading), (iRow / iTotalRows) * 100)
				If LoadResultsBackgroundWorker.CancellationPending Then
					Exit For
				End If
			End If
		Next
	End Sub

	Private Delegate Sub SetResultsLoadingCallback(ByVal dblValue As Double)
	Private Sub SetResultsLoading(ByVal dblValue As Double)
		ResultsLoading.LoadingBar.Value = dblValue
	End Sub

	Private Delegate Sub SetResultsLoadingLabelCallback(ByVal strValue As String)
	Private Sub SetResultsLoadingLabel(ByVal strValue As String)
		ResultsLoading.lblLoading.Content = strValue
	End Sub

	Private Delegate Sub DisplayResultsCallback()
	Private Sub DisplayResults()
		For i As Integer = 1 To ResultData.dvFinal.Table.Columns.Count - 1
			Dim dgc As New DataGridTextColumn
			If i = 1 Then
				dgc.Header = ResultData.dvFinal.Table.Columns(i).ColumnName.Replace("{", "").Replace("}", "")
				Dim DatabaseHeaderStyle As New Style
				DatabaseHeaderStyle.Setters.Add(New Setter(FontWeightProperty, FontWeights.Bold))
				DatabaseHeaderStyle.Setters.Add(New Setter(ForegroundProperty, New SolidColorBrush(Color.FromArgb(255, 255, 255, 255))))
				DatabaseHeaderStyle.Setters.Add(New Setter(BackgroundProperty, New SolidColorBrush(Color.FromArgb(150, 0, 0, 0))))
				dgc.HeaderStyle = DatabaseHeaderStyle
				dgc.FontWeight = FontWeights.Bold
			Else
				dgc.Header = ResultData.dvFinal.Table.Columns(i).ColumnName
			End If
			Dim b As New Binding(ResultData.dvFinal.Table.Columns(i).ColumnName)
			b.TargetNullValue = "NULL"
			dgc.Binding = b
			dgResults.Columns.Add(dgc)
		Next

		dgResults.ItemsSource = ResultData.dvFinal

		IsComparing = False
	End Sub

	' Edit-in-place code
	'Private Sub dgResults_BeginningEdit(ByVal sender As Object, ByVal e As DataGridBeginningEditEventArgs) Handles dgResults.BeginningEdit
	'	e.Cancel = False

	'	If e.Column.DisplayIndex = 0 Then
	'		e.Cancel = True
	'	Else
	'		Dim colIndex As Integer = e.Column.DisplayIndex + 1
	'		Dim rowIndex As Integer = e.Row.GetIndex

	'		If ResultData.dvFinal.Table.Rows(rowIndex).RowError = "Blank" Then
	'			e.Cancel = True
	'		Else
	'			If ResultData.dvFinal.Table.Rows(rowIndex)(1).ToString = "A" Then
	'				If ResultData.dvFinal.Table.Columns(colIndex).ColumnName = _KeyA Then
	'					e.Cancel = True
	'				End If
	'			ElseIf ResultData.dvFinal.Table.Rows(rowIndex)(1).ToString = "B" Then
	'				If ResultData.dvFinal.Table.Columns(colIndex).ColumnName = _KeyB Then
	'					e.Cancel = True
	'				End If
	'			End If
	'		End If
	'	End If
	'End Sub

	'Private Sub dgResults_CellEditEnding(ByVal sender As Object, ByVal e As DataGridCellEditEndingEventArgs) Handles dgResults.CellEditEnding
	'	If e.EditAction = DataGridEditAction.Commit AndAlso e.EditingElement IsNot Nothing Then
	'		'Base.WriteToConsole(Me, DirectCast(e.EditingElement, TextBox).Text)
	'		'Base.WriteToConsole(Me, ResultData.dvFinal.Table.Columns(e.Column.DisplayIndex + 1).ColumnName)
	'		'Base.WriteToConsole(Me, "Row " & e.Row.GetIndex.ToString())

	'		Try
	'			Dim colIndex As Integer = e.Column.DisplayIndex + 1
	'			Dim rowIndex As Integer = e.Row.GetIndex
	'			Dim column As String = ResultData.dvFinal.Table.Columns(colIndex).ColumnName

	'			If ResultData.dvFinal.Table.Rows(rowIndex)(1).ToString = "A" Then
	'				Dim cmd As New SqlCommand("UPDATE " & DatabaseNameA & ".dbo." & _TableA & " SET [" & column & "] = @updatevalue WHERE [" & _KeyA & "] = @keyvalue", Base.sqlConn(CChar("A")))
	'				cmd.Parameters.AddWithValue("@keyvalue", ResultData.dvFinal.Table.Rows(rowIndex)(_KeyA))
	'				cmd.Parameters.AddWithValue("@updatevalue", DirectCast(e.EditingElement, TextBox).Text)
	'				If cmd.ExecuteNonQuery() > 0 Then
	'					Base.WriteToConsole(Me, "Update successful on column [" & column & "]")
	'				Else
	'					Base.WriteToConsole(Me, "Update failed on column [" & column & "]")
	'					e.Cancel = True
	'				End If
	'			ElseIf ResultData.dvFinal.Table.Rows(rowIndex)(1).ToString = "B" Then
	'				Dim cmd As New SqlCommand("UPDATE " & DatabaseNameB & ".dbo." & _TableB & " SET [" & column & "] = @updatevalue WHERE [" & _KeyB & "] = @keyvalue", Base.sqlConn(CChar("B")))
	'				cmd.Parameters.AddWithValue("@keyvalue", ResultData.dvFinal.Table.Rows(rowIndex)(_KeyB))
	'				cmd.Parameters.AddWithValue("@updatevalue", DirectCast(e.EditingElement, TextBox).Text)
	'				If cmd.ExecuteNonQuery() > 0 Then
	'					Base.WriteToConsole(Me, "Update successful on column [" & column & "]")
	'				Else
	'					Base.WriteToConsole(Me, "Update failed on column [" & column & "]")
	'					e.Cancel = True
	'				End If
	'			End If
	'		Catch ex As Exception
	'			Base.WriteToConsole(Me, ex.ToString)
	'			Base.WriteToConsole(Me, "Update failed")
	'			e.Cancel = True
	'		End Try
	'	End If
	'End Sub

End Class
